package com.wsoft.form.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.wsoft.core.exception.Asserts;
import com.wsoft.form.constant.MysqlReservedWordConstant;
import com.wsoft.form.dto.WfLinkQuerysDTO;
import com.wsoft.form.dto.WfLinkWheresDTO;
import com.wsoft.form.entity.*;
import com.wsoft.form.enums.FieldKindEnum;
import com.wsoft.form.enums.FormEnum;
import com.wsoft.form.manager.*;
import com.wsoft.form.mapper.WfFormTableMapper;
import com.wsoft.form.query.column.ColumnInsertQuery;
import com.wsoft.form.query.column.ColumnListQuery;
import com.wsoft.form.query.column.ColumnUpdateQuery;
import com.wsoft.form.service.IWfFormColumnService;
import com.wsoft.form.vo.ColumnLinkVO;
import com.wsoft.form.vo.TableColumnVO;
import com.wsoft.utils.NumberUtil;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

/**
 * <p>
 * 自定义表单-字段表 服务实现类
 * </p>
 *
 * @author Z001
 * @since 2024年08月29日 09:11:06
 */
@Service
public class WfFormColumnServiceImpl implements IWfFormColumnService {

    @Resource
    private WfFormTableManager tableManager;
    @Resource
    private WfFormManager wfFormManager;
    @Resource
    private WfFormColumnManager columnManager;
    @Resource
    private WfFormTableMapper wfFormTableMapper;
    @Resource
    private WfFormColumnLinkManager linkManager;
    @Resource
    private WfFormTableRelationManager relationManager;
    @Resource
    private WfFormVirtualColumnManager virtualColumnManager;


    /**
     * notLengthSqlType
     */
    private final List<Integer> NOT_LENGTH_SQL_TYPE = Arrays.asList(2,5,6,7,8,9);

    /**
     * 新增字段
     *
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean insertColumn(ColumnInsertQuery query) {

        WfFormTableEntity tableEntity = tableManager.getById(query.getTableId());
        if (null == tableEntity) {
            return false;
        }
        checkColumnName(tableEntity, query.getColumnName());

        WfFormColumnEntity columnEntity = Convert.convert(WfFormColumnEntity.class, query);
        columnEntity.setPrimaryKey(FormEnum.ZERO.getValue());
        columnEntity.setIsDefault(FormEnum.ZERO.getValue());
        buildDataType(columnEntity);
        //保存column
        columnManager.save(columnEntity);
        //保存 字段关联关系
        this.saveLinkColumn(query, columnEntity.getId());

        if (FormEnum.ONE.getValue().equals(tableEntity.getIsCreateTable())) {
            //修改表结构
            String columnSql = createColumnSql(columnEntity);
            wfFormTableMapper.addColumn(tableEntity.getTableName(), columnSql);
        }
        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateColumn(ColumnUpdateQuery query) {
        WfFormColumnEntity oldColumn = columnManager.getById(query.getId());

        //修改
        WfFormColumnEntity columnEntity = Convert.convert(WfFormColumnEntity.class, query);
        buildDataType(columnEntity);


        WfFormTableEntity tableEntity = tableManager.getById(query.getTableId());
        if(!oldColumn.getColumnName().equals(query.getColumnName())){
            //判断能否修改

            //是否有link关联
            boolean exist = linkManager.isExist(query.getId());
            if(exist){
                Asserts.fail("[wfrom.update.column.error]");
            }
            //是否有聚合关联
            List<WfFormVirtualColumnEntity> virtualList = virtualColumnManager.getByAggregationTableId(null, query.getId());
            if(CollUtil.isNotEmpty(virtualList)){
                Asserts.fail("[wfrom.update.column.error]");
            }

           //修改字段名
            wfFormTableMapper.changeColumn(tableEntity.getTableName(),oldColumn.getColumnName(),query.getColumnName(),columnEntity.getFullColumnType());
        }else if(!NOT_LENGTH_SQL_TYPE.contains(columnEntity.getFieldKind()) &&
                StrUtil.isNotBlank(oldColumn.getDataType()) &&
                !oldColumn.getDataType().equals(columnEntity.getDataType())){
            wfFormTableMapper.updateColumn(tableEntity.getTableName(),oldColumn.getColumnName(),columnEntity.getFullColumnType());
        }

        //修改column表
        columnManager.updateById(columnEntity);


        //编辑字段关联关系
        this.updateLinkColumn(query);

        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean delete(Long columnId) {
        WfFormColumnEntity columnEntity = columnManager.getById(columnId);
        if(FormEnum.ONE.getValue().equals(columnEntity.getIsDefault())){
            Asserts.fail("[wform.system.column]");
        }

        WfFormTableEntity tableEntity = tableManager.getById(columnEntity.getTableId());
        List<WfFormTableRelationEntity> relationList = relationManager.getBySlaveColumnId(columnEntity.getFormId(), columnId);
        if (CollUtil.isNotEmpty(relationList)) {
            //该字段被关联,无法删除
            Asserts.fail("[wform.slave.column.already.associated]");
        }
        List<WfFormVirtualColumnEntity> aggregationColumnList = virtualColumnManager.getByAggregationColumn(columnId);
        if (CollUtil.isNotEmpty(aggregationColumnList)) {
            //该字段被聚合字段关联,无法删除
            Asserts.fail("[wform.column.already.aggregation.associated]");
        }

        //删除字段
        columnManager.removeById(columnId);

        //删除表结构
        if (FormEnum.ONE.getValue().equals(tableEntity.getIsCreateTable())) {
            //创建了实体表的 才去删除
            wfFormTableMapper.dropColumn(tableEntity.getTableName(), columnEntity.getColumnName());
        }
        //移除关联关系
        linkManager.remove(Collections.singletonList(columnId));
        return true;
    }

    @Override
    public TableColumnVO get(Long columnId) {
        WfFormColumnEntity entity = columnManager.getById(columnId);
        TableColumnVO convert = Convert.convert(TableColumnVO.class, entity);

        //判断是否有关联字段
        WfFromColumnLinkEntity link = linkManager.getByColumnId(columnId);
        if (null != link) {
            WfFormEntity formEntity = wfFormManager.getById(link.getLinkFormId());
            ColumnLinkVO linkVO = Convert.convert(ColumnLinkVO.class, link);
            convert.setLinkFormName(formEntity.getFormName());
            convert.setLinkFormId(linkVO.getLinkFormId());
            if (StrUtil.isNotBlank(linkVO.getLinkFromQuerys())) {
                convert.setQuerysList(JSONObject.parseArray(linkVO.getLinkFromQuerys(), WfLinkQuerysDTO.class));
            }
            if(StrUtil.isNotBlank(linkVO.getLinkFormWheres())){
                convert.setWheresList(JSONObject.parseArray(linkVO.getLinkFormWheres(), WfLinkWheresDTO.class));
            }
        }
        return convert;
    }

    @Override
    public PageInfo<WfFormColumnEntity> listColumn(ColumnListQuery query) {
        PageHelper.startPage(query.getPageNum(), query.getPageSize());
        List<WfFormColumnEntity> list = columnManager.listColumn(query);
        return PageInfo.of(list);
    }


    //############################### 私有函数 ###############################

    /**
     * 保存关联字段和关联条件
     *
     * @param query    请求参数
     * @param columnId 保存的字段id
     */
    private void saveLinkColumn(ColumnInsertQuery query, Long columnId) {
        WfFromColumnLinkEntity link = new WfFromColumnLinkEntity();
        if (null != query.getLinkFormId() && CollUtil.isNotEmpty(query.getQuerysList())) {
            //校验
            checkQuerysList(query.getQuerysList());

            //关联字段
            link.setLinkFormId(query.getLinkFormId());
            link.setDesignId(query.getDesignId());
            link.setColumnMasterId(query.getColumnMasterId());
            //当前保存的字段id
            link.setColumnId(columnId);
            List<WfLinkQuerysDTO> querysList = query.getQuerysList();
            querysList = querysList.stream().peek(item -> {
                //-1 代表保存的是当前字段,该字段尚未保存,所以赋值columnId
                if (-1 == item.getColumnId()) {
                    item.setColumnId(columnId);
                }
            }).collect(Collectors.toList());
            link.setLinkFromQuerys(JSONObject.toJSONString(querysList));

            //关联条件
            if (CollUtil.isNotEmpty(query.getWheresList())) {
                List<WfLinkWheresDTO> wfLinkWheresDTOList = query.getWheresList().stream().peek(item -> item.setColumnId(columnId)).collect(Collectors.toList());
                link.setLinkFormWheres(JSONObject.toJSONString(wfLinkWheresDTOList));
            }
            linkManager.save(link);
        }
    }

    /**
     * 修改关联关系 和 关联条件
     *
     * @param query
     */
    private void updateLinkColumn(ColumnUpdateQuery query) {
        Long columnId = query.getId();
        if (null == query.getLinkFormId()) {
            //如果关联条件不存在, 则删除
            linkManager.remove(Collections.singletonList(columnId));
        } else {
            //调更新接口
            WfFromColumnLinkEntity linkEntity = linkManager.getByColumnId(columnId);
            if (null == linkEntity) {
                linkEntity = new WfFromColumnLinkEntity();
                linkEntity.setLinkFormId(query.getLinkFormId());
                linkEntity.setColumnId(columnId);
                linkEntity.setDesignId(query.getDesignId());
                linkEntity.setColumnMasterId(query.getColumnMasterId());
                linkEntity.setLinkFromQuerys(JSONObject.toJSONString(query.getQuerysList()));
                if (CollUtil.isNotEmpty(query.getWheresList())) {
                    linkEntity.setLinkFormWheres(JSONObject.toJSONString(query.getWheresList()));
                }
                linkManager.save(linkEntity);
            }else{
                linkEntity.setLinkFormId(query.getLinkFormId());
                linkEntity.setDesignId(query.getDesignId());
                linkEntity.setColumnMasterId(query.getColumnMasterId());
                linkEntity.setColumnId(columnId);
                if(CollUtil.isNotEmpty(query.getQuerysList())){
                    linkEntity.setLinkFromQuerys(JSONObject.toJSONString(query.getQuerysList()));
                }
                if (CollUtil.isNotEmpty(query.getWheresList())) {
                    linkEntity.setLinkFormWheres(JSONObject.toJSONString(query.getWheresList()));
                }else{
                    linkEntity.setLinkFormWheres(null);
                }
                linkManager.updateById(linkEntity);
            }
        }
    }

    /**
     * 校验关联字段
     */
    private void checkQuerysList(List<WfLinkQuerysDTO> querysList) {
        long count = querysList.stream().filter(item -> FormEnum.ONE.getValue().equals(item.getIsMaster())).count();
        if (count != 1) {
            //不能有多个或者没有主字段
            Asserts.fail("[wform.link.column.one]");
        }
    }




    /**
     * 校验字段名 是否存在
     *
     * @param tableEntity table
     * @param columnName  字段名
     */
    private void checkColumnName(WfFormTableEntity tableEntity, String columnName) {
        if (FormEnum.ZERO.getValue().equals(tableEntity.getIsCreateTable())) {
            //未创建实体表,不能新增字段
            Asserts.fail("[wform.column.cannot.insert]");
        }
        //判断是否该字段
        WfFormColumnEntity columnEntity = columnManager.getByTableIdAndColumnName(tableEntity.getId(), columnName);
        if (null != columnEntity) {
            //字段名已存在
            Asserts.fail("[wform.table.column.exist]");
        }

        if(!isValidColumnName(columnName)){
            //字段不能为数据库关键字
            Asserts.fail("[wform.column.name.not.keywords]");
        }
    }

    /**
     * 校验是否 符合mysql 字段名规范
     * @param columnName
     * @return
     */
    private boolean isValidColumnName(String columnName) {
        // 字段名不能为空
        if (columnName == null || columnName.isEmpty()) {
            return false;
        }

        // 字段名长度不能超过64个字符
        if (columnName.length() > MysqlReservedWordConstant.COLUMN_SIZE) {
            return false;
        }

        // 字段名首字符必须是字母或下划线
        char firstChar = columnName.charAt(0);
        if (!Character.isLetter(firstChar) && firstChar != StrUtil.C_UNDERLINE) {
            return false;
        }

        // 字段名不能包含空格
        if (columnName.contains(MysqlReservedWordConstant.SPACE)) {
            return false;
        }

        // 字段名只能包含字母、数字、下划线和美元符号
        for (char c : columnName.toCharArray()) {
            if (!Character.isLetterOrDigit(c) && c != '_' && c != '$') {
                return false;
            }
        }

        // 字段名不能是MySQL的保留字
        if (MysqlReservedWordConstant.MYSQL_RESERVED_WORD_PREFIX.contains(columnName.toUpperCase()) ||
                MysqlReservedWordConstant.DATA_PERM_LIST.contains(columnName.toUpperCase())) {
            return false;
        }
        return true;
    }

    /**
     * 组装字段sql
     *
     * @param column
     * @return
     */
    private String createColumnSql(WfFormColumnEntity column) {
        String blackSpace = " ";
        StringBuilder sb = new StringBuilder();
        sb.append("`").append(column.getColumnName()).append("`").append(blackSpace)
                .append(column.getFullColumnType()).append(blackSpace);
        if (FormEnum.ZERO.getValue().equals(column.getValidIsNull())) {
            sb.append("not null").append(blackSpace);
        } else {
            sb.append("null").append(blackSpace);
        }
        sb.append("comment").append(blackSpace).
                append("'").append(column.getColumnComment()).append("'");
        if (FormEnum.ONE.getValue().equals(column.getPrimaryKey())) {
            sb.append(blackSpace).append("primary key");
        }
        return sb.toString();
    }


    /**
     * 构建数据库字段类型
     * @param columnEntity
     */
    private void buildDataType(WfFormColumnEntity columnEntity){
        if(FieldKindEnum.DECIMAL.getFieldKind().equals(columnEntity.getFieldKind())
        && StrUtil.isNotBlank(columnEntity.getDataType())){
            //校验  输入的格式
            if(!NumberUtil.isValidDecimalFormatString(columnEntity.getDataType())){
                Asserts.fail("[wform.decimal.format.error]");
            }
        }
        FieldKindEnum kindEnum = FieldKindEnum.resolveFieldKind(columnEntity.getFieldKind());
        columnEntity.setColumnType(kindEnum.getSqlType());

        if(NOT_LENGTH_SQL_TYPE.contains(columnEntity.getFieldKind())){
            //不需要指定长度
            columnEntity.setFullColumnType(kindEnum.getSqlType());
            if(StrUtil.isBlank(columnEntity.getDataType())){
                columnEntity.setDataType(kindEnum.getValue());
            }
        }else if(kindEnum.getFieldKind().equals(FieldKindEnum.VARCHAR.getFieldKind()) ||
                kindEnum.getFieldKind().equals(FieldKindEnum.INTEGER.getFieldKind())){
            //需要指定长度
            if(StrUtil.isBlank(columnEntity.getDataType())){
                //未指定长度的 赋默认值
                columnEntity.setFullColumnType(kindEnum.getSqlType() + "(" + kindEnum.getValue() + ")" );
            }else{
                //指定长度了的 取用户输入的值
                columnEntity.setFullColumnType(kindEnum.getSqlType() + "(" + columnEntity.getDataType() +  ")");
            }
        }
        else{
            columnEntity.setFullColumnType(kindEnum.getSqlType() + "(" + kindEnum.getValue() + ")" );
        }
    }



}
